#include "stdafx.h"

#include "StaffTbl.h"

CStaffTbl::CStaffTbl()
{
	m_sName = _T("nhan_vien");
}
CStaff* CStaffTbl::getStaff(CString sMSNV)
{
	CStaff *pStaff = NULL;
	MYSQL_RES * pResult = NULL;
	CString sNewQuery;
	mysql_query(conn,"SET character_set_results=utf8");
	sNewQuery.Format(_T("SELECT * FROM %s.%s where ma_so_nhan_vien = '%s'"),g_sDatabase,m_sName,sMSNV);
	CT2A ascii(sNewQuery, CP_UTF8);		
	mysql_query(conn,ascii.m_psz);
	pResult = mysql_use_result(conn);
	if (pResult)
	{
		MYSQL_ROW row = mysql_fetch_row(pResult);
		if (row)
		{
			pStaff = new CStaff;
			pStaff->setCode(CString(row[0]));
			pStaff->setName(CString (CA2W(row[1], CP_UTF8)));//name	
			pStaff->setDate(CString(row[2]));//Date
			pStaff->setAdress(CString(CA2W(row[3], CP_UTF8)));//village
			pStaff->setProvisionally(CString(CA2W(row[4], CP_UTF8))); //provisionally
			pStaff->setIdCard(CString(row[5]));
			pStaff->setPhoneNumber(CString(row[6]));
			pStaff->setEmail(CString(row[7]));
			pStaff->setPosition(CString(CA2W(row[8], CP_UTF8)));
			pStaff->setPermisson(atoi(row[9]));
			pStaff->setSex(atoi(row[10]));
			pStaff->setStore(CString(row[11]));
			pStaff->setSalary(CString(row[12]));
			pStaff->setTotalTime(CString(row[13]));
		
		}
		mysql_free_result(pResult);
	}
	return pStaff;
}

int CStaffTbl::getPermissonByCode(const CString& sCode)
{
	int nPermission = 0;
	MYSQL_RES * pResult = NULL;
	CString sNewQuery;
	mysql_query(conn,"SET character_set_results=utf8");
	sNewQuery.Format(_T("SELECT %s FROM %s.%s where %s = '%s'"),FIELD_PERMISSION_STAFF,g_sDatabase,TABLE_NAME_STAFF,FIELD_CODE_STAFF,sCode);
	CT2A ascii(sNewQuery, CP_UTF8);		
	mysql_query(conn,ascii.m_psz);
	pResult = mysql_use_result(conn);
	if (pResult)
	{
		MYSQL_ROW row = mysql_fetch_row(pResult);
		if (row)
		{			
			nPermission = atoi(row[0]);			

		}
		mysql_free_result(pResult);
	}
	return nPermission;
}
bool CStaffTbl::updateStaff(CStaff*pStaff)
{

	bool bResult = false;
	if (pStaff)
	{
		CString sQuery;
		MYSQL_ROW row = NULL;
		sQuery.Format(_T("UPDATE %s.%s SET "),g_sDatabase,TABLE_NAME_STAFF);
		CString stemp;
		stemp.Format(_T("%s=_utf8'%s',"),FIELD_NAME_STAFF,pStaff->getName());
		sQuery += stemp;
		stemp.Format(_T("%s='%s',"),FIELD_DATE_BORN_STAFF,pStaff->getDate());
		sQuery += stemp;
		stemp.Format(_T("%s= _utf8'%s',"),FIELD_VILLAGE_STAFF,pStaff->getAdress());
		sQuery += stemp;
		stemp.Format(_T("%s=_utf8'%s',"),FIELD_PLACE_STAFF,pStaff->getProvisionally());
		sQuery += stemp;
		stemp.Format(_T("%s='%s',"),FIELD_ID_CARD_STAFF,pStaff->getIdCard());
		sQuery += stemp;
		stemp.Format(_T("%s='%s',"),FIELD_PHONE_STAFF,pStaff->getPhoneNumber());
		sQuery += stemp;
		stemp.Format(_T("%s='%s',"),FIELD_EMAIL_STAFF,pStaff->getEmail());
		sQuery += stemp;
		stemp.Format(_T("%s='%s',"),FIELD_FUNCTION_STAFF,pStaff->getPosition());	
		sQuery += stemp;
		stemp.Format(_T("%s=%d,"),FIELD_SEX_STAFF,pStaff->getSex());
		sQuery += stemp;
		stemp.Format(_T("%s='%s',"),FIELD_STORE_STAFF,pStaff->getStore());
		sQuery += stemp;
		stemp.Format(_T("%s='%s',"),FIELD_SALARY_STAFF,pStaff->getSalary());
		sQuery += stemp;
		stemp.Format(_T("%s='%s'"),FIELD_TIME_STAFF,pStaff->getTotalTime());
		sQuery += stemp;
		
		CString sAddition;
		sAddition.Format(_T(" WHERE %s ='%s'"),FIELD_CODE_STAFF,pStaff->getCode());
		
		sQuery += sAddition;	
		CT2A ascii(sQuery, CP_UTF8);	
		if(mysql_query(conn,ascii.m_psz)==0)
			bResult = true;
	}
	return bResult;

}

bool CStaffTbl::updatePermision(const CString& sCode,const int& nPermision)
{
	bool bResult = false;
	if (conn)
	{
		CString sQuery;
		MYSQL_ROW row = NULL;
		sQuery.Format(_T("UPDATE %s.%s SET "),g_sDatabase,TABLE_NAME_STAFF);
		CString stemp;
		stemp.Format(_T("%s=%d"),FIELD_PERMISSION_STAFF,nPermision);
		sQuery += stemp;

		CString sAddition;
		sAddition.Format(_T(" WHERE %s ='%s'"),FIELD_CODE_STAFF,sCode);
		sQuery += sAddition;	
		CT2A ascii(sQuery, CP_UTF8);	
		if(mysql_query(conn,ascii.m_psz)==0)
			bResult = true;
	}
	return bResult;
}
bool CStaffTbl::updateCheckPoint(CString sCode, CString sTotal)
{
	bool bResult = false;
	CString sQuery;
	MYSQL_ROW row = NULL;
	sQuery.Format(_T("UPDATE %s.%s SET %s='%s' WHERE %s ='%s'"),
		g_sDatabase,TABLE_NAME_STAFF,
		FIELD_TIME_STAFF,sTotal,		
		FIELD_CODE_STAFF,sCode);		
	CT2A ascii(sQuery, CP_UTF8);	
	if(mysql_query(conn,ascii.m_psz)==0)
		bResult = true;
	return bResult;
}
 bool CStaffTbl::insertStaff(CStaff*pStaff)
 {	
	 CString sValue = _T("(");
	 if (pStaff)
	 {
		 CString stemp;
		 stemp.Format(_T("'%s',"),pStaff->getCode());
		 sValue += stemp;
		 stemp.Format(_T("_utf8'%s',"),pStaff->getName());
		 sValue += stemp;
		 stemp.Format(_T("'%s',"),pStaff->getDate());
		 sValue += stemp;
		 stemp.Format(_T("_utf8'%s',"),pStaff->getAdress());
		 sValue += stemp;
		 stemp.Format(_T("_utf8'%s',"),pStaff->getProvisionally());
		 sValue += stemp;
		 stemp.Format(_T("'%s',"),pStaff->getIdCard());
		 sValue += stemp;
		 stemp.Format(_T("'%s',"),pStaff->getPhoneNumber());
		 sValue += stemp;
		 stemp.Format(_T("'%s',"),pStaff->getEmail());
		 sValue += stemp;
		 stemp.Format(_T("'%s',"),pStaff->getPosition());
		 sValue += stemp;
		 stemp.Format(_T("%d,"),pStaff->getPermisson());
		 sValue += stemp;
		 stemp.Format(_T("%d,"),pStaff->getSex());
		 sValue += stemp;
		 stemp.Format(_T("'%s',"),pStaff->getStore());
		 sValue += stemp;
		 stemp.Format(_T("'%s',"),pStaff->getSalary());
		 sValue += stemp;
		 stemp.Format(_T("'%s'"),pStaff->getTotalTime());
		 sValue += stemp;

		 sValue+= _T(")");

		 return insertRecord(TABLE_NAME_STAFF,sValue);
	 }
	 return false;	
	
 }
 vector<CStaff*> CStaffTbl::getListStaff(int nPermission, CString sStore)
{
	vector<CStaff*> vtStaff;

	MYSQL_RES * pResult = NULL;
	CString sNewQuery;
	mysql_query(conn,"SET character_set_results=utf8");
	if (nPermission == ID_ADMIN)
		sNewQuery.Format(_T("SELECT * FROM %s.%s where %s >= '%d'"),g_sDatabase,TABLE_NAME_STAFF,FIELD_PERMISSION_STAFF,nPermission);
	else
		sNewQuery.Format(_T("SELECT * FROM %s.%s where %s > '%d' AND %s = '%s'"),g_sDatabase,TABLE_NAME_STAFF,FIELD_PERMISSION_STAFF,nPermission,FIELD_STORE_STAFF,sStore);
	CT2A ascii(sNewQuery, CP_UTF8);		
	mysql_query(conn,ascii.m_psz);
	pResult = mysql_use_result(conn);
	if (pResult)
	{
		MYSQL_ROW row = NULL;
		while((row = mysql_fetch_row(pResult)) )
		{
			CStaff *pStaff = new CStaff;
			pStaff->setCode(CString(row[0]));
			pStaff->setName(CString (CA2W(row[1], CP_UTF8)));//name	
			pStaff->setDate(CString(row[2]));//Date
			pStaff->setAdress(CString(CA2W(row[3], CP_UTF8)));//village
			pStaff->setProvisionally(CString(CA2W(row[4], CP_UTF8))); //provisionally
			pStaff->setIdCard(CString(row[5]));
			pStaff->setPhoneNumber(CString(row[6]));
			pStaff->setEmail(CString(row[7]));
			pStaff->setPosition(CString(CA2W(row[8], CP_UTF8)));
			pStaff->setPermisson(atoi(row[9]));
			pStaff->setSex(atoi(row[10]));
			pStaff->setStore(CString(row[11]));
			pStaff->setSalary(CString(row[12]));
			pStaff->setTotalTime(CString(row[13]));
			vtStaff.push_back(pStaff);
		}
		mysql_free_result(pResult);
	}
	return vtStaff;
}

vector<CStaff*> CStaffTbl::getListStaff()
{
	vector<CStaff*> vtStaff;

	MYSQL_RES * pResult = NULL;
	CString sNewQuery;
	mysql_query(conn,"SET character_set_results=utf8");	
	sNewQuery.Format(_T("SELECT * FROM %s.%s"),g_sDatabase,TABLE_NAME_STAFF);
	CT2A ascii(sNewQuery, CP_UTF8);		
	mysql_query(conn,ascii.m_psz);
	pResult = mysql_use_result(conn);
	if (pResult)
	{
		MYSQL_ROW row = NULL;
		while((row = mysql_fetch_row(pResult)) )
		{
			CStaff *pStaff = new CStaff;
			pStaff->setCode(CString(row[0]));
			pStaff->setName(CString (CA2W(row[1], CP_UTF8)));//name	
			pStaff->setDate(CString(row[2]));//Date
			pStaff->setAdress(CString(CA2W(row[3], CP_UTF8)));//village
			pStaff->setProvisionally(CString(CA2W(row[4], CP_UTF8))); //provisionally
			pStaff->setIdCard(CString(row[5]));
			pStaff->setPhoneNumber(CString(row[6]));
			pStaff->setEmail(CString(row[7]));
			pStaff->setPosition(CString(CA2W(row[8], CP_UTF8)));
			pStaff->setPermisson(atoi(row[9]));
			pStaff->setSex(atoi(row[10]));
			pStaff->setStore(CString(row[11]));
			pStaff->setSalary(CString(row[12]));
			pStaff->setTotalTime(CString(row[13]));
			vtStaff.push_back(pStaff);
		}
		mysql_free_result(pResult);
	}
	return vtStaff;
}
 int  CStaffTbl::getMaxCode()
 {
	 int nMax = 0;
	 MYSQL_RES * pResult = NULL;

	 CString sNewQuery;
	 mysql_query(conn,"SET character_set_results=utf8");
	 sNewQuery.Format(_T("SELECT %s ,MAX(%s)FROM %s.%s "),FIELD_CODE_STAFF,FIELD_CODE_STAFF,g_sDatabase,TABLE_NAME_STAFF);
	 CT2A ascii(sNewQuery, CP_UTF8);		
	 mysql_query(conn,ascii.m_psz);
	 pResult = mysql_use_result(conn);
	 if (pResult)
	 {
		 MYSQL_ROW row = mysql_fetch_row(pResult);
		 if (row)
		 {
			 CString sTemplate;
			 sTemplate = row[1];
			 sTemplate.Replace(_T("NV"),_T("0"));
			 nMax = _tstoi(sTemplate);
		 }
		 mysql_free_result(pResult);
	 }
	 return nMax;
 }